```html
<!DOCTYPE html>
<html lang="zh-CN">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>JDBC 开发指南 | 技术小馆</title>
    <link href="https://cdn.staticfile.org/tailwindcss/2.2.19/tailwind.min.css" rel="stylesheet">
    <link href="https://cdn.staticfile.org/font-awesome/6.4.0/css/all.min.css" rel="stylesheet">
    <link href="https://fonts.googleapis.com/css2?family=Noto+Serif+SC:wght@400;500;600;700&family=Noto+Sans+SC:wght@300;400;500;700&display=swap" rel="stylesheet">
    <script src="https://cdn.jsdelivr.net/npm/mermaid@latest/dist/mermaid.min.js"></script>
    <style>
        :root {
            --primary: #4361ee;
            --primary-light: #3f37c9;
            --secondary: #3a0ca3;
            --accent: #f72585;
            --dark: #1a1a2e;
            --light: #f8f9fa;
        }
        body {
            font-family: 'Noto Sans SC', 'Noto Serif SC', Tahoma, Arial, Roboto, "Droid Sans", "Helvetica Neue", "Droid Sans Fallback", "Heiti SC", "Hiragino Sans GB", Simsun, sans-serif;
            background-color: #f5f7fa;
            color: #333;
            line-height: 1.6;
        }
        .hero {
            background: linear-gradient(135deg, #4361ee 0%, #3a0ca3 100%);
            color: white;
            padding: 6rem 1rem;
            position: relative;
            overflow: hidden;
        }
        .hero::before {
            content: "";
            position: absolute;
            top: 0;
            left: 0;
            width: 100%;
            height: 100%;
            background-image: url('');
            opacity: 0.2;
        }
        .hero-title {
            font-size: 3.5rem;
            font-weight: 700;
            margin-bottom: 1.5rem;
            text-shadow: 0 2px 10px rgba(0,0,0,0.2);
        }
        .hero-subtitle {
            font-size: 1.5rem;
            font-weight: 300;
            max-width: 800px;
            margin: 0 auto 2rem;
            opacity: 0.9;
        }
        .section {
            padding: 4rem 1rem;
            max-width: 1200px;
            margin: 0 auto;
        }
        .section-title {
            font-size: 2.2rem;
            font-weight: 600;
            color: var(--dark);
            margin-bottom: 2rem;
            position: relative;
            padding-bottom: 0.5rem;
        }
        .section-title::after {
            content: "";
            position: absolute;
            bottom: 0;
            left: 0;
            width: 60px;
            height: 4px;
            background: var(--accent);
            border-radius: 2px;
        }
        .card {
            background: white;
            border-radius: 8px;
            box-shadow: 0 5px 15px rgba(0,0,0,0.05);
            padding: 2rem;
            margin-bottom: 2rem;
            transition: transform 0.3s ease, box-shadow 0.3s ease;
        }
        .card:hover {
            transform: translateY(-5px);
            box-shadow: 0 10px 25px rgba(0,0,0,0.1);
        }
        .card-title {
            font-size: 1.5rem;
            font-weight: 600;
            color: var(--primary);
            margin-bottom: 1rem;
            display: flex;
            align-items: center;
        }
        .card-title i {
            margin-right: 0.5rem;
            color: var(--accent);
        }
        .code-block {
            background: #282c34;
            color: #abb2bf;
            border-radius: 6px;
            padding: 1.5rem;
            font-family: 'Courier New', Courier, monospace;
            margin: 1.5rem 0;
            overflow-x: auto;
            position: relative;
        }
        .code-block::before {
            content: "";
            position: absolute;
            top: 0;
            left: 0;
            width: 100%;
            height: 40px;
            background: rgba(255,255,255,0.05);
            border-top-left-radius: 6px;
            border-top-right-radius: 6px;
        }
        .code-block .lang {
            position: absolute;
            top: 10px;
            left: 15px;
            color: rgba(255,255,255,0.5);
            font-size: 0.8rem;
        }
        table {
            width: 100%;
            border-collapse: collapse;
            margin: 1.5rem 0;
            font-size: 0.95rem;
        }
        table th {
            background: var(--primary);
            color: white;
            padding: 0.75rem;
            text-align: left;
        }
        table td {
            padding: 0.75rem;
            border-bottom: 1px solid #eee;
        }
        table tr:nth-child(even) {
            background: #f9f9f9;
        }
        table tr:hover {
            background: #f1f1f1;
        }
        .badge {
            display: inline-block;
            padding: 0.25rem 0.5rem;
            border-radius: 4px;
            font-size: 0.75rem;
            font-weight: 600;
            background: var(--primary-light);
            color: white;
            margin-right: 0.5rem;
            margin-bottom: 0.5rem;
        }
        .footer {
            background: var(--dark);
            color: white;
            padding: 3rem 1rem;
            text-align: center;
        }
        .footer a {
            color: var(--accent);
            text-decoration: none;
            transition: color 0.3s ease;
        }
        .footer a:hover {
            color: white;
            text-decoration: underline;
        }
        .highlight {
            background: rgba(246, 211, 101, 0.3);
            padding: 0.2rem 0.4rem;
            border-radius: 4px;
        }
        .note {
            background: rgba(67, 97, 238, 0.1);
            border-left: 4px solid var(--primary);
            padding: 1rem;
            margin: 1.5rem 0;
            border-radius: 0 4px 4px 0;
        }
        .diagram-container {
            background: white;
            padding: 2rem;
            border-radius: 8px;
            box-shadow: 0 5px 15px rgba(0,0,0,0.05);
            margin: 2rem 0;
        }
        .step {
            display: flex;
            align-items: flex-start;
            margin-bottom: 1.5rem;
            position: relative;
            padding-left: 3rem;
        }
        .step-number {
            position: absolute;
            left: 0;
            top: 0;
            width: 2rem;
            height: 2rem;
            background: var(--primary);
            color: white;
            border-radius: 50%;
            display: flex;
            align-items: center;
            justify-content: center;
            font-weight: 600;
        }
        .step-content {
            flex: 1;
        }
        .step-title {
            font-weight: 600;
            margin-bottom: 0.5rem;
            color: var(--primary-light);
        }
        .timeline {
            position: relative;
            padding-left: 2rem;
            margin: 2rem 0;
        }
        .timeline::before {
            content: "";
            position: absolute;
            left: 7px;
            top: 0;
            bottom: 0;
            width: 2px;
            background: var(--primary);
        }
        .timeline-item {
            position: relative;
            padding-bottom: 2rem;
        }
        .timeline-item::before {
            content: "";
            position: absolute;
            left: -2rem;
            top: 5px;
            width: 12px;
            height: 12px;
            border-radius: 50%;
            background: var(--accent);
            border: 3px solid var(--primary);
        }
        .timeline-date {
            font-weight: 600;
            color: var(--primary-light);
            margin-bottom: 0.5rem;
        }
    </style>
</head>
<body>
    <!-- Hero Section -->
    <section class="hero text-center">
        <div class="container mx-auto">
            <h1 class="hero-title">JDBC 开发指南</h1>
            <p class="hero-subtitle">Java 数据库连接技术的完整指南，从基础概念到高级应用，掌握数据库操作的核心技巧</p>
            <div class="flex justify-center space-x-4">
                <a href="#introduction" class="px-6 py-3 bg-white text-primary rounded-full font-medium hover:bg-opacity-90 transition">开始学习</a>
                <a href="#examples" class="px-6 py-3 bg-transparent border-2 border-white text-white rounded-full font-medium hover:bg-white hover:bg-opacity-10 transition">查看示例</a>
            </div>
        </div>
    </section>

    <!-- Introduction Section -->
    <section id="introduction" class="section">
        <h2 class="section-title">一、引言</h2>
        
        <div class="card">
            <h3 class="card-title"><i class="fas fa-database"></i> 1.1 如何操作数据库</h3>
            <p>使用客户端工具访问数据库，需要手工建立连接，输入用户名和密码登录，编写 SQL 语句，点击执行，查看操作结果（结果集或受影响行数）。</p>
        </div>

        <div class="card">
            <h3 class="card-title"><i class="fas fa-question-circle"></i> 1.2 实际开发中，会采用客户端操作数据库吗？</h3>
            <p>在实际开发中，当用户的数据发生改变时，不可能通过客户端操作执行 SQL 语句，因为操作量过大，无法保证效率和正确性。</p>
        </div>
    </section>

    <!-- JDBC Section -->
    <section id="jdbc" class="section bg-gray-50">
        <h2 class="section-title">二、JDBC（Java Database Connectivity）</h2>
        
        <div class="card">
            <h3 class="card-title"><i class="fas fa-plug"></i> 2.1 什么是 JDBC？</h3>
            <p>JDBC（Java Database Connectivity） Java 连接数据库的规范（标准），可以使用 Java 语言连接数据库完成 CRUD 操作。</p>
        </div>

        <div class="card">
            <h3 class="card-title"><i class="fas fa-lightbulb"></i> 2.2 JDBC 核心思想</h3>
            <p>Java 中定义了访问数据库的接口，可以为多种关系型数据库提供统一的访问方式。由数据库厂商提供驱动实现类（Driver 数据库驱动）。</p>
            
            <div class="note">
                <h4 class="font-bold mb-2">2.2.1 MySQL 数据库驱动</h4>
                <ul class="list-disc pl-5">
                    <li>mysql-connector-java-5.1.X 适用于 5.X 版本</li>
                    <li>mysql-connector-java-8.0.X 适用于 8.X版本</li>
                </ul>
            </div>

            <h4 class="font-bold mt-4 mb-2 text-primary-light">2.2.2 JDBC API</h4>
            <p>JDBC 是由多个接口和类进行功能实现。</p>
            
            <table>
                <thead>
                    <tr>
                        <th>类型</th>
                        <th>权限定名</th>
                        <th>简介</th>
                    </tr>
                </thead>
                <tbody>
                    <tr>
                        <td>class</td>
                        <td>java.sql.DriverManager</td>
                        <td>管理多个数据库驱动类，提供了获取数据库连接的方法</td>
                    </tr>
                    <tr>
                        <td>interface</td>
                        <td>java.sql.Connection</td>
                        <td>代表一个数据库连接（当connection不是null时，表示已连接数据库）</td>
                    </tr>
                    <tr>
                        <td>interface</td>
                        <td>java.sql.Statement</td>
                        <td>发送SQL语句到数据库工具</td>
                    </tr>
                    <tr>
                        <td>interface</td>
                        <td>java.sql.ResultSet</td>
                        <td>保存SQL查询语句的结果数据（结果集）</td>
                    </tr>
                    <tr>
                        <td>class</td>
                        <td>java.sql.SQLException</td>
                        <td>处理数据库应用程序时所发生的异常</td>
                    </tr>
                </tbody>
            </table>
        </div>

        <div class="card">
            <h3 class="card-title"><i class="fas fa-cogs"></i> 2.3 环境搭建</h3>
            <div class="step">
                <div class="step-number">1</div>
                <div class="step-content">
                    <p class="step-title">创建 lib 文件夹</p>
                    <p>在项目下新建 lib 文件夹，用于存放 jar 文件。</p>
                </div>
            </div>
            <div class="step">
                <div class="step-number">2</div>
                <div class="step-content">
                    <p class="step-title">复制驱动文件</p>
                    <p>将 mysql 驱动 mysql-connector-java-8.X 复制到项目的 lib 文件夹中。</p>
                </div>
            </div>
            <div class="step">
                <div class="step-number">3</div>
                <div class="step-content">
                    <p class="step-title">添加依赖</p>
                    <p>选中 lib 文件夹右键 Add as Library，点击 OK。</p>
                </div>
            </div>
        </div>
    </section>

    <!-- JDBC Steps Section -->
    <section id="jdbc-steps" class="section">
        <h2 class="section-title">三、JDBC 开发步骤【重点】</h2>
        
        <div class="card">
            <h3 class="card-title"><i class="fas fa-hashtag"></i> 3.1 注册驱动</h3>
            <p>使用 Class.forName("com.mysql.jdbc.Driver");手动加载字节码文件到 JVM 中。</p>
            <div class="code-block">
                <span class="lang">java</span>
                <pre>Class.forName("com.mysql.jdbc.Driver");//加载驱动</pre>
            </div>
            <p>在MySQL中创建一张表</p>
            <div class="code-block">
                <span class="lang">sql</span>
                <pre>DROP TABLE IF EXISTS `t_user`;
CREATE TABLE `t_user`  (
  `id` int(0) NOT NULL AUTO_INCREMENT,
  `username` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `age` int(0) NULL DEFAULT NULL,
  `email` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;</pre>
            </div>
        </div>

        <div class="card">
            <h3 class="card-title"><i class="fas fa-link"></i> 3.2 连接数据库</h3>
            <ul class="list-disc pl-5 mb-4">
                <li>通过 DriverManager.getConnection(url,user,password) 获取数据库连接对象</li>
                <li>URL: jdbc:mysql://localhost:3306/database</li>
                <li>username: root</li>
                <li>password: root</li>
            </ul>
            <div class="code-block">
                <span class="lang">java</span>
                <pre>Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/database?useUnicode=true&characterEncoding=utf8", "root","1234");</pre>
            </div>
            <div class="note">
                <p><strong>URL(Uniform Resource Locator)</strong> 统一资源定位符：由协议、IP、端口、SID（程序实例名称）组成</p>
            </div>
        </div>

        <div class="card">
            <h3 class="card-title"><i class="fas fa-paper-plane"></i> 3.3 获取发送 SQL 的对象</h3>
            <p>通过 Connection 对象获得 Statement 对象，用于对数据库进行通用访问。</p>
            <div class="code-block">
                <span class="lang">java</span>
                <pre>Statement statement = conn.createStatement();</pre>
            </div>
        </div>

        <div class="card">
            <h3 class="card-title"><i class="fas fa-play"></i> 3.4 执行SQL 语句</h3>
            <p>执行 SQL 语句并接收执行结果。</p>
            <div class="code-block">
                <span class="lang">java</span>
                <pre>String sql ="INSERT INTO t_user(username,age,email) VALUES('admin',20,'admin@qq.com')";
       
int result = statement.executeUpdate(sql);//执行SQL语句并接收结果</pre>
            </div>
            <div class="note">
                <ul class="list-disc pl-5">
                    <li>注意：在编写 DML 语句时，一定要注意字符串参数的符号是单引号 '值'</li>
                    <li>DML 语句：增删改时，返回受影响行数（int 类型）。</li>
                    <li>DQL 语句：查询时，返回结果数据(ResultSet 结果集)。</li>
                </ul>
            </div>
        </div>

        <div class="card">
            <h3 class="card-title"><i class="fas fa-tasks"></i> 3.5 处理结果</h3>
            <p>接受处理操作结果。</p>
            <div class="code-block">
                <span class="lang">java</span>
                <pre>if(result == 1){
    System.out.println("Success");
}</pre>
            </div>
            <ul class="list-disc pl-5 mt-4">
                <li>受影响行数：逻辑判断、方法返回。</li>
                <li>查询结果集：迭代、依次获取。</li>
            </ul>
        </div>

        <div class="card">
            <h3 class="card-title"><i class="fas fa-recycle"></i> 3.6 释放资源</h3>
            <p>遵循先开后关原则，释放所使用到的资源对象。</p>
            <div class="code-block">
                <span class="lang">java</span>
                <pre>statement.close();
conn.close();</pre>
            </div>
        </div>

        <div class="card">
            <h3 class="card-title"><i class="fas fa-laptop-code"></i> 3.7 综合案例</h3>
            <p>整合以上核心六步，实现向数据库表中插入一条数据。</p>
            <div class="code-block">
                <span class="lang">java</span>
                <pre>package com.ts.JDBC;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

public class DeleteJdbc {
    public static void main(String[] args) throws Exception{
        //1.加载驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        //2.获得连接对象
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc-demo","root","root");
        //3.获得执行SQL的对象
        Statement statement = connection.createStatement();
        //4.执行SQL语句，并接收结果
        int result = statement.executeUpdate("delete from t_user where id = 1;");
        //5.处理结果
        if(result==1){
            System.out.println("删除成功！");
        }else{
            System.out.println("删除失败！");
        }
        //6.释放资源
        statement.close();
        connection.close();
    }
}</pre>
            </div>
        </div>
    </section>

    <!-- ResultSet Section -->
    <section id="resultset" class="section bg-gray-50">
        <h2 class="section-title">四、ResultSet（结果集）</h2>
        <p class="mb-6">在执行查询 SQL 后，存放查询到的结果集数据。</p>
        
        <div class="card">
            <h3 class="card-title"><i class="fas fa-table"></i> 4.1 接收结果集</h3>
            <p>ResultSet rs = statement.executeQuery(sql);</p>
            <div class="code-block">
                <span class="lang">java</span>
                <pre>ResultSet rs= statement.executeQuery("SELECT * FROM t_employees;");</pre>
            </div>
        </div>

        <div class="card">
            <h3 class="card-title"><i class="fas fa-list-ol"></i> 4.2 遍历 ResultSet 中的数据</h3>
            <p>ResultSet 以表（table）结构进行临时结果的存储，需要通过 JDBC API 将其中数据进行依次获取。</p>
            
            <ul class="list-disc pl-5 mt-4">
                <li>数据行指针：初始位置在第一行数据前，每调用一次 boolean next()方法ResultSet 的指针向下移动一行，结果为 true，表示当前行有数据。</li>
                <li>rs.getXxx(整数);代表根据列的编号顺序获得，从 1 开始。</li>
                <li>rs.getXxx("列名");代表根据列名获得。</li>
            </ul>
            
            <div class="code-block mt-4">
                <span class="lang">java</span>
                <pre>boolean next() throws SQLException  //判断 rs 结果集中下一行是否存在数据</pre>
            </div>

            <h4 class="font-bold mt-6 mb-2 text-primary-light">4.2.1 遍历方法</h4>
            <div class="code-block">
                <span class="lang">java</span>
                <pre>int getInt(int columnIndex) throws SQLException //获得当前行第N列的int值
int getInt(String columnLabel) throws SQLException //获得当前行columnLabel列的int值

double getDouble(int columnIndex) throws SQLException //获得当前行第N列的double值
double getDouble(String columnLabel) throws SQLException //获得当前行columnLabel列的double值

String getString(int columnIndex) throws SQLException //获得当前行第N列的String值
String getString(String columnLabel) throws SQLException //获得当前行columnLabel列的String值

......</pre>
            </div>
            <div class="note">
                <p>注意：列的编号从 1 开始。</p>
            </div>
        </div>

        <div class="card">
            <h3 class="card-title"><i class="fas fa-code"></i> 4.3 综合案例</h3>
            <p>对 t_user 表中的所有数据进行遍历。</p>
            
            <h4 class="font-bold mt-4 mb-2 text-primary-light">4.3.1 根据列的名称获取</h4>
            <div class="code-block">
                <span class="lang">java</span>
                <pre>package com.ts.www.test;

import java.sql.*;

public class JobsQuery {
    public static void main(String[] args) {
            //1.加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");

            //2.获取数据库连接对象
            Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc-demo","root","root");
      
            //3.获取发送 sql 语句对象
            Statement statement = connection.createStatement();

            //4.执行 SQL 语句并接收结果集
            ResultSet resultSet = statement.executeQuery("select  * from t_user");
            //5 处理结果集
            while(resultSet.next()){
                //5.1有数据，依据列名获取数据
                String username = resultSet.getString("username");
                String email = resultSet.getString("email");
                int age = resultSet.getInt("age");
                int id = resultSet.getInt("id");
                System.out.println(id+"\t"+username+"\t"+email+"\t"+username);
            }
       
                //6.释放资源
               rs.close();
               statement.close();
              connection.close();
        }
    }
    }
}</pre>
            </div>

            <h4 class="font-bold mt-4 mb-2 text-primary-light">4.3.2 根据列的编号获取数据</h4>
            <div class="code-block">
                <span class="lang">java</span>
                <pre>//。。。。与上无异
            while(resultSet.next()){
                //5.2有数据，依据列名获取数据
                String job_id = resultSet.getString(1);
                String job_title = resultSet.getString(2);
                int min_salary = resultSet.getInt(3);
                int max_salary = resultSet.getInt(4);
                System.out.println(job_id+"\t"+job_title+"\t"+min_salary+"\t"+max_salary);
            }
            //释放资源</pre>
            </div>
        </div>
    </section>

    <!-- Common Errors Section -->
    <section id="errors" class="section">
        <h2 class="section-title">五、常见错误</h2>
        
        <div class="card">
            <ul class="list-disc pl-5">
                <li><span class="font-medium">java.lang.ClassNotFoundException</span>：找不到类（类名书写错误、没有导入jar包）</li>
                <li><span class="font-medium">java.sql.SQLException</span>：与sql语句相关的错误 （约束错误、表名列名书写错误） 建议：在客户端工具中测试SQL语句之后再粘贴在代码中</li>
                <li><span class="font-medium">com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column</span> 原因：列值Sting类型没有加单引号</li>
                <li><span class="font-medium">Duplicate entry '1' for key 'PRIMARY'</span> 原因，主键值已存在或混乱，更改主键值或清空表</li>
                <li><span class="font-medium">com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'password' in</span></li>
                <li>原因：可能输入的值的类型不对，确定是否插入的元素时对应的值的类型正确</li>
            </ul>
        </div>
    </section>

    <!-- Login Case Section -->
    <section id="login-case" class="section bg-gray-50">
        <h2 class="section-title">六、综合案例【登录】</h2>
        
        <div class="card">
            <h3 class="card-title"><i class="fas fa-table"></i> 6.1 创建表</h3>
            <ul class="list-disc pl-5">
                <li>创建一张用户表 User</li>
                <li>id ，主键、自动增长。</li>
                <li>用户名，字符串类型，唯一、非空</li>
                <li>密码，字符串类型，非空</li>
                <li>手机号码，字符串类型</li>
                <li>插入 2 条测试语句</li>
            </ul>
        </div>

        <div class="card">
            <h3 class="card-title"><i class="fas fa-sign-in-alt"></i> 6.2 实现登录</h3>
            <ul class="list-disc pl-5">
                <li>通过控制台用户输入用户名和密码。</li>
                <li>用户输入的用户名和密码作为条件，编写查询 SQL 语句。</li>
                <li>如果该用户存在，提示登录成功，反之提示失败。</li>
            </ul>
        </div>
    </section>

    <!-- SQL Injection Section -->
    <section id="sql-injection" class="section">
        <h2 class="section-title">七、SQL注入问题</h2>
        
        <div class="card">
            <h3 class="card-title"><i class="fas fa-shield-alt"></i> 7.1 什么是 SQL 注入</h3>
            <p>用户输入的数据中有 SQL 关键字或语法并且参与了 SQL 语句的编译，导致 SQL 语句编译后的条件含义为 true，一直得到正确的结果。这种现象称为 SQL 注入。</p>
        </div>

        <div class="card">
            <h3 class="card-title"><i class="fas fa-lock"></i> 7.2 如何避免 SQL 注入</h3>
            <p>由于编写的 SQL 语句是在用户输入数据，整合后再进行编译。所以为了避免 SQL 注入的问题，我们要使 SQL 语句在用户输入数据前就已进行编译成完整的 SQL 语句，再进行填充数据。</p>
        </div>
    </section>

    <!-- PreparedStatement Section -->
    <section id="prepared-statement" class="section bg-gray-50">
        <h2 class="section-title">八、PreparedStatement【重点】</h2>
        <p class="mb-6">PreparedStatement 继承了 Statement 接口，执行 SQL 语句的方法无异。</p>
        
        <div class="card">
            <h3 class="card-title"><i class="fas fa-magic"></i> 8.1 PreparedStatement的应用</h3>
            <p>作用：</p>
            <ul class="list-disc pl-5">
                <li>预编译SQL 语句，效率高。</li>
                <li>安全，避免SQL注入 。</li>
                <li>可以动态的填充数据，执行多个同构的 SQL 语句。</li>
                <li>编译一次，绑定SQL后只能只能执行当前sql，可以执行多次(只是变量不同，sql结构都是相同的)</li>
            </ul>

            <h4 class="font-bold mt-4 mb-2 text-primary-light">8.1.1 参数标记</h4>
            <div class="code-block">
                <span class="lang">java</span>
                <pre>//1.预编译 SQL 语句
PreparedStatement pstmt = conn.prepareStatement("select * from user where username=? and password=?");</pre>
            </div>
            <div class="note">
                <p>注意：JDBC中的所有参数都由 ？符号占位，这被称为参数标记。在执行SQL语句之前，必须为每个参数提供值。</p>
            </div>

            <h4 class="font-bold mt-4 mb-2 text-primary-light">8.1.2 动态参数绑定</h4>
            <p>pstmt.setXxx(下标,值) 参数下标从 1 开始，为指定参数下标绑定值</p>
            <div class="code-block">
                <span class="lang">java</span>
                <pre>//1.预编译 SQL 语句
PreparedStatement pstmt = conn.prepareStatement("select * from user where username=? and password=?");   
//2.为参数下标赋值
pstmt.setString(1,username);
pstmt.setString(2,password);</pre>
            </div>
        </div>
    </section>

    <!-- Utility Class Section -->
    <section id="utility-class" class="section">
        <h2 class="section-title">九、封装工具类</h2>
        <div class="card">
            <ul class="list-disc pl-5">
                <li>在实际JDBC的使用中，存在着大量的重复代码:例如连接数据库、关闭数据库等这些操作！</li>
                <li>我们需要把传统的JDBC代码进行重构，抽取出通用的JDBC工具类！以后连接任何数据库、释放资源都可以使用这个工具类。</li>
            </ul>
            
            <h3 class="font-bold mt-6 mb-2 text-primary-light">9.1 重用性方案</h3>
            <ul class="list-disc pl-5">
                <li>封装获取连接、释放资源两个方法。</li>
                <li>提供public static Connection getConnection(){}方法。</li>
                <li>提供public static void closeAll(Connection conn , Statement sm , ResultSet rs){}方法。</li>
            </ul>

            <h4 class="font-bold mt-4 mb-2 text-primary-light">9.1.1 重用工具类实现</h4>
            <div class="code-block">
                <span class="lang">java</span>
                <pre>package com.ts.JDBC;

import java.sql.*;

/**
 * 重用性方案
 * 获取连接
 * 释放资源
 */
public class DBUtils {

    static {//类加载，执行一次！
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
    //1.获取连接
    public static Connection getConnection() {
        Connection connection = null;
        try {
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc-demo", "root", "root");
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }

    //2.释放资源
    public static void closeAll(Connection connection, Statement statement, ResultSet resultSet) {
        try {
            if (resultSet != null) {
                resultSet.close();
            }
            if (statement != null) {
                statement.close();
            }
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}</pre>
            </div>

            <h3 class="font-bold mt-6 mb-2 text-primary-light">9.2 跨平台方案</h3>
            <div class="code-block">
                <span class="lang">java</span>
                <pre>public static final Properties prop = new Properties(); //读取配置文件的Map

static{  //首次使用工具类时，加载驱动InputStream is = JDBCUtil.class.getResourceAsStream("路径"); //通过复用本类自带流，读取jdbc.properties配置文件。classPath = bin
    
    prop.load(is); //通过prop对象将流中的配置信息分割成键值对
  
    String driverName = prop.getProperty("driver"); //通过driverName的键获取对应的值（com.mysql.jdbc.Driver）
  
    Class.forName(driverName);   //加载驱动
}</pre>
            </div>

            <h4 class="font-bold mt-4 mb-2 text-primary-light">9.2.1 跨平台工具类实现</h4>
            <p>在src 目录下新建 db.properties 文件。</p>
            <div class="code-block">
                <span class="lang">properties</span>
                <pre>driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbc-demo
user=root
password=root</pre>
            </div>

            <p>工具类的封装。</p>
            <div class="code-block">
                <span class="lang">java</span>
                <pre>package com.ts.jdbc2;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class DBUtils {
    private static final Properties PROPERTIES = new Properties();//存储配置文件的map

    static {

        InputStream is = DBUtils.class.getResourceAsStream("/db.properties");

        try {
            PROPERTIES.load(is);//通过流，将配置文件内容加载到properties集合
            Class.forName(PROPERTIES.getProperty("driver"));
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection() {
        Connection connection = null;
        try {
            connection = DriverManager.getConnection(PROPERTIES.getProperty("url"), PROPERTIES.getProperty("username"), PROPERTIES.getProperty("password"));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }

    public static void closeAll(Connection connection, Statement statement, ResultSet resultSet) {
        try {
            if (resultSet != null) {
                resultSet.close();
            }
            if (statement != null) {
                statement.close();
            }
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}</pre>
            </div>
        </div>
    </section>

    <!-- ORM Section -->
    <section id="orm" class="section bg-gray-50">
        <h2 class="section-title">十、ORM</h2>
        <div class="card">
            <p>ORM（Object Relational Mapping），从数据库查询到的结果集(ResultSet)在进行遍历时，逐行遍历，取出的都是零散的数据。在实际应用开发中，我们需要将零散的数据进行封装整理。</p>
            
            <h3 class="font-bold mt-6 mb-2 text-primary-light">10.1 实体类(entity)：零散数据的载体</h3>
            <ul class="list-disc pl-5">
                <li>一行数据中，多个零散的数据进行整理。</li>
                <li>通过entity的规则对表中的数据进行对象的封装。</li>
                <li>表名=类名；列名=属性名；提供各个属性的get、set方法。</li>
                <li>提供无参构造方法、(视情况添加有参构造)。</li>
            </ul>

            <h4 class="font-bold mt-4 mb-2 text-primary-light">10.1.1 ORM应用</h4>
            <p>entity实体类</p>
            <div class="code-block">
                <span class="lang">java</span>
                <pre>package com.ts.www.test;

public class T_Jobs {
    private String job_id;
    private String job_title;
    private int min_salary;
    private int max_salary;

    @Override
    public String toString() {
        return "T_Jobs{" +
                "job_id='" + job_id + '\'' +
                ", job_title='" + job_title + '\'' +
                ", min_salary=" + min_salary +
                ", max_salary=" + max_salary +
                '}';
    }

    public String getJob_id() {
        return job_id;
    }

    public void setJob_id(String job_id) {
        this.job_id = job_id;
    }

    public String getJob_title() {
        return job_title;
    }

    public void setJob_title(String job_title) {
        this.job_title = job_title;
    }

    public int getMin_salary() {
        return min_salary;
    }

    public void setMin_salary(int min_salary) {
        this.min_salary = min_salary;
    }

    public int getMax_salary() {
        return max_salary;
    }

    public void setMax_salary(int max_salary) {
        this.max_salary = max_salary;
    }

    public T_Jobs() {
    }

    public T_Jobs(String job_id, String job_title, int min_salary, int max_salary) {
        this.job_id = job_id;
        this.job_title = job_title;
        this.min_salary = min_salary;
        this.max_salary = max_salary;
    }
}</pre>
            </div>

            <p>查询结果封装</p>
            <div class="code-block">
                <span class="lang">java</span>
                <pre>package com.ts.www.test;

import java.sql.*;

public class JobsQuery {
    public static void main(String[] args) {

        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;

        String URL = "jdbc:mysql://localhost:3306/jdbc-demo";
        String user = "root";
        String password = "root";

        try {
            //1.加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");

            //2.获取数据库连接对象
            connection = DriverManager.getConnection(URL, user, password);

            //3.编写 SQL 语句
            String sql = "select  * from t_jobs";

            //4.获取发送 sql 语句对象
            statement = connection.createStatement();

            //5.执行 SQL 语句并接收结果集
            resultSet = statement.executeQuery(sql);
            //5.1使用 while 循环判断下一行是否有数据
            while(resultSet.next()){
                //5.2有数据，依据列名获取数据
                String job_id = resultSet.getString(1);
                String job_title = resultSet.getString(2);
                int min_salary = resultSet.getInt(3);
                int max_salary = resultSet.getInt(4);
                //5.3 创建实体类对象
                T_Jobs t_jobs = new T_Jobs();
                //5.4 每列数据对应属性进行赋值
                t_jobs.setJob_id(job_id);
                t_jobs.setJob_title(job_title);
                t_jobs.setMin_salary(min_salary);
                t_jobs.setMax_salary(max_salary);
                System.out.println(t_jobs);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                //6.释放资源
                if (resultSet != null) {
                    resultSet.close();
                }
                if (statement != null) {
                    statement.close();
                }
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}</pre>
            </div>
        </div>
    </section>

    <!-- DAO Section -->
    <section id="dao" class="section">
        <h2 class="section-title">十一、DAO 数据访问对象（Data Access Object）</h2>
        <div class="card">
            <ul class="list-disc pl-5">
                <li>DAO 实现了业务逻辑与数据库访问相分离。</li>
                <li>对同一张表的所有操作封装在XxxDaoImpl对象中。</li>
                <li>根据增删改查的不同功能实现具体的方法（insert、update、delete、select、selectAll）。</li>
            </ul>
            
            <h3 class="font-bold mt-6 mb-2 text-primary-light">11.1 创建数据库</h3>
            <ul class="list-disc pl-5">
                <li>创建一张表 Person，有以下列：</li>
                <li>id：int，主键，自动增长</li>
                <li>name：varchar(20) 非空</li>
                <li>age：int 非空</li>
                <li>birthday：Date</li>
                <li>email：字符串</li>
                <li>address：字符串</li>
            </ul>

            <h3 class="font-bold mt-6 mb-2 text-primary-light">11.2 封装实体类</h3>
            <p>创建entity实体类 Person，编写属性私有化，构造方法，get/set 方法。</p>

            <h3 class="font-bold mt-6 mb-2 text-primary-light">11.3 编写 DaoImpl 类</h3>
            <p>编写 DaoImpl 类，提供增删改查方法，使用 JDBC 开发步骤，完成功能。</p>
            <div class="code-block">
                <span class="lang">java</span>
                <pre>package com.ts.person;

import sun.awt.image.DataBufferNative;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * 增、删、改、查单个、查所有
 * 只做数据库访问操作！不参与逻辑判断
 * 数据库一张表的访问的操作复用！
 */
public class PersonDaoImpl {
    //新增
    public int  insert(Person person){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        String sql = "insert into person(name,age,borndate,email,address) values(?,?,?,?,?);";
        try {
            connection = DBUtils.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,person.getName());
            preparedStatement.setInt(2,person.getAge());
            preparedStatement.setDate(3,null);
            preparedStatement.setString(4,person.getEmail());
            preparedStatement.setString(5,person.getAddress());
            int result = preparedStatement.executeUpdate();
            return result;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtils.closeAll(connection,preparedStatement,null);
        }
        return 0;
    }
    //修改
    public int update(Person person){
        Connection connection =null;
        PreparedStatement preparedStatement =  null;
        String sql = "update person set name=?,age=?,borndate=?,email=?,address=? where id = ?";
        try {
            connection = DBUtils.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,person.getName());
            preparedStatement.setInt(2,person.getAge());
            preparedStatement.setDate(3,null);
            preparedStatement.setString(4,person.getEmail());
            preparedStatement.setString(5,person.getAddress());
            preparedStatement.setInt(6,person.getId());
            int result = preparedStatement.executeUpdate();
            return result;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtils.closeAll(connection,preparedStatement,null);
        }
        return 0;
    }
    //删除
    public int delete(int id){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        String sql = "delete from person where id= ?;";
        connection = DBUtils.getConnection();
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,id);
            int result = preparedStatement.executeUpdate();
            return result;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtils.closeAll(connection,preparedStatement,null);
        }
        return 0;
    }
    //查单个
    public Person select(int id){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        String sql = "select * from person where id = ?;";
        Person person = null;
        try {
            connection = DBUtils.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,id);
            resultSet = preparedStatement.executeQuery();
            if(resultSet.next()){
                person = new Person();
                int pid= resultSet.getInt("id");
                String name = resultSet.getString("name");
                int age = resultSet.getInt("age");
                Date bornDate = resultSet.getDate("borndate");
                String email = resultSet.getString("email");
                String address = resultSet.getString("address");
                person.setId(pid);
                person.setName(name);
                person.setAge(age);
                person.setBornDate(bornDate);
                person.setEmail(email);
                person.setAddress(address);
            }
            return person;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtils.closeAll(connection,preparedStatement,resultSet);
        }
        return null;
    }
    //查所有
    public List<Person> selectAll(){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Person person = null;
        List<Person> personList = new ArrayList<>();

        try {
            connection = DBUtils.getConnection();
            preparedStatement = connection.prepareStatement("select * from person;");
            resultSet = preparedStatement.executeQuery();

            while(resultSet.next()){
                int pid= resultSet.getInt("id");
                String name = resultSet.getString("name");
                int age = resultSet.getInt("age");
                Date bornDate = resultSet.getDate("borndate");
                String email = resultSet.getString("email");
                String address = resultSet.getString("address");

                person = new Person(pid,name,age,bornDate,email,address);
                personList.add(person);
            }
            return personList;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
}</pre>
            </div>
        </div>
    </section>

    <!-- Date Utils Section -->
    <section id="date-utils" class="section bg-gray-50">
        <h2 class="section-title">十二、Date工具类</h2>
        <div class="card">
            <p>现有问题：数据库存储的数据类型为java.sql.Date。而我们Java应用层存储日期类型为java.util.Date。当我们用Java应用程序插入带有日期的数据到数据库中时，需要进行转换。</p>
            
            <h3 class="font-bold mt-6 mb-2 text-primary-light">12.1 java.util.Date</h3>
            <ul class="list-disc pl-5">
                <li>Java语言常规应用层面的日期类型，可以通过字符串创建对应的时间对象。</li>
                <li>无法直接通过JDBC插入到数据库。</li>
            </ul>

            <h3 class="font-bold mt-6 mb-2 text-primary-light">12.2 java.sql.Date</h3>
            <ul class="list-disc pl-5">
                <li>不可以通过字符串创建对应的时间对象，只能通过毫秒值创建对象(1970年至今的毫秒值)。</li>
                <li>可以直接通过JDBC插入到数据库。</li>
            </ul>

            <h3 class="font-bold mt-6 mb-2 text-primary-light">12.3 SimpleDateFormat</h3>
            <p>格式化和解析日期的具体类。允许进行格式化（日期 -> 文本）、解析（文本 -> 日期）和规范化。</p>

            <h4 class="font-bold mt-4 mb-2 text-primary-light">12.3.1 SimpleDateFormat应用</h4>
            <div class="code-block">
                <span class="lang">java</span>
                <pre>SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");//指定日期格式

java.util.Date date = sdf.parse(String dateStr);//将字符串解析成日期类型（java.util.Date）

String dates = sdf.format(date);//将日期格式化成字符串</pre>
            </div>

            <h3 class="font-bold mt-6 mb-2 text-primary-light">12.4 封装DateUtils工具类</h3>
            <div class="code-block">
                <span class="lang">java</span>
                <pre>package com.qf.day43.t4;

import java.text.ParseException;
import java.text.SimpleDateFormat;

/**
 * 日期转换
 * 字符串转UtilDate
 * UtilDate转SqlDate
 * utilDate转成字符串
 */
public class DateUtils {
    private static final SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");

    //字符串转Util
    public static java.util.Date strToUtilDate(String str) {
        try {
            return simpleDateFormat.parse(str);
        } catch (ParseException e) {
            e.printStackTrace();
        }
        return null;
    }
    //util转sql
    public static java.sql.Date utilToSql(java.util.Date date){
        return new java.sql.Date(date.getTime());
    }
	
    //util转字符串
    public static String toStr(java.util.Date bornDate){
        return sdf.format(bornDate);
    }
}</pre>
            </div>
        </div>
    </section>

    <!-- Service Layer Section -->
    <section id="service-layer" class="section">
        <h2 class="section-title">十三、Service业务逻辑层</h2>
        
        <div class="card">
            <h3 class="card-title"><i class="fas fa-cogs"></i> 13.1 什么是业务？</h3>
            <p>代表用户完成的一个业务功能，可以由一个或多个DAO的调用组成。（软件所提供的一个功能都叫业务）。</p>
            
            <h3 class="font-bold mt-6 mb-2 text-primary-light">13.2 Service开发流程</h3>
            <h4 class="font-bold mt-4 mb-2 text-primary-light">13.2.1 编写 service 实现转账功能</h4>
            <div class="code-block">
                <span class="lang">java</span>
                <pre>package com.ts.day44.accounts;

import java.sql.Connection;
import java.sql.SQLException;

public class T_AccountServiceImpl {
    /**
     * 转账业务
     *
     * @param fromNo 转账卡号
     * @param pwd    转账卡号密码
     * @param toNo   收钱卡号
     * @param money  转账金额
     */
    public String transfer(String fromNo, String pwd, String toNo, double money) {//收参
        String result = "转账失败！";
        //2.组织业务功能
        T_AccountDaoImpl accountDao = new T_AccountDaoImpl();

        try {
            //2.1验证fromNo是否存在
            T_Account fromAcc = accountDao.select(fromNo);

            if (fromAcc == null) {
                throw new RuntimeException("----卡号不存在-----");
            }
            //2.2验证fromNo的密码是否正确
            if (!fromAcc.getPassword().equals(pwd)) {
                throw new RuntimeException("----密码错误----");
            }
            //2.3验证余额是否充足
            if (fromAcc.getBalance() < money) {
                throw new RuntimeException("----余额不足----");
            }
            //2.4验证toNo是否存在
            T_Account toAcc = accountDao.select(toNo);

            if (toAcc == null) {
                throw new RuntimeException("----对方卡号不存在----");
            }
            //2.5减少fromNo的余额
            //修改自己的金额，将余额-转账金额替换原有的属性
            fromAcc.setBalance(fromAcc.getBalance() - money);

            accountDao.update(fromAcc);
            
            //2.6增加toNo的余额
            toAcc.setBalance(toAcc.getBalance() + money);
            accountDao.update(toAcc);
            result = "转账成功！";
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            DBUtils.closeAll(connection,null,null);
        }
        return result;
    }
}</pre>
            </div>
        </div>
    </section>

    <!-- Transaction Section -->
    <section id="transaction" class="section bg-gray-50">
        <h2 class="section-title">十四、事务</h2>
        <div class="card">
            <p>在JDBC 中，获得 Connection 对象开始事务--提交或回滚--关闭连接。其事务策略是</p>
            <div class="code-block">
                <span class="lang">java</span>
                <pre>conn.setAutoCommit(false);//true 等价于 1，false 等价于 0
conn.commit();//手动提交事务
conn.rollback();//手动回滚事务</pre>
            </div>
            
            <h3 class="font-bold mt-6 mb-2 text-primary-light">14.1 service 层控制事务</h3>
            <div class="code-block">
                <span class="lang">java</span>
                <pre>package com.ts.day44.accounts;

import java.sql.Connection;
import java.sql.SQLException;

public class T_AccountServiceImpl {

    /**
     * 转账业务
     *
     * @param fromNo 转账卡号
     * @param pwd    转账卡号密码
     * @param toNo   收钱卡号
     * @param money  转账金额
     */
    public String transfer(String fromNo, String pwd, String toNo, double money) {//收参
        String result = "转账失败！";
        //2.组织业务功能
        T_AccountDaoImpl accountDao = new T_AccountDaoImpl();

        //拿一个连接
        Connection connection = null;

        try {
            //建立了一个数据库连接
            connection = DBUtils.getConnection();
            //开启事务！ 并且关闭事务的自动提交
            connection.setAutoCommit(false);
            //2.1验证fromNo是否存在
            T_Account fromAcc = accountDao.select(fromNo);

            if (fromAcc == null) {
                throw new RuntimeException("----卡号不存在-----");
            }
            //2.2验证fromNo的密码是否正确
            if (!fromAcc.getPassword().equals(pwd)) {
                throw new RuntimeException("----密码错误----");
            }
            //2.3验证余额是否充足
            if (fromAcc.getBalance() < money) {
                throw new RuntimeException("----余额不足----");
            }
            //2.4验证toNo是否存在
            T_Account toAcc = accountDao.select(toNo);

            if (toAcc == null) {
                throw new RuntimeException("----对方卡号不存在----");
            }

            //2.5减少fromNo的余额
            //修改自己的金额，将余额-转账金额替换原有的属性
            fromAcc.setBalance(fromAcc.getBalance() - money);

            accountDao.update(fromAcc);
			
            int a = 10/0;//模拟程序转账出现异常！
            
            //2.6增加toNo的余额
            toAcc.setBalance(toAcc.getBalance() + money);
            accountDao.update(toAcc);
            result = "转账成功！";
            //执行到这里，没有异常，则提交事务！
            connection.commit();
        } catch (Exception e) {
            e.printStackTrace();
            try {
                //出现异常，回滚！
                System.out.println("出现了异常！回滚整个事务！");
                connection.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }

        }finally {
            DBUtils.closeAll(connection,null,null);
        }
        return result;
    }
}</pre>
            </div>
            <div class="note">
                <p>问题：当转账程序出现异常，事务控制成功了吗？</p>
            </div>

            <h3 class="font-bold mt-6 mb-2 text-primary-light">14.2 解决方案1：传递 Connection</h3>
            <p>为了解决线程中Connection 对象不同步的问题，可以将 Connection对象通过 service传递给各个DAO 方法吗？</p>
            
            <h4 class="font-bold mt-4 mb-2 text-primary-light">14.2.1 传递的问题</h4>
            <ul class="list-disc pl-5">
                <li>如果使用传递Connection，容易造成接口污染（BadSmell）。</li>
                <li>定义接口是为了更容易更换实现，而将 Connection定义在接口中，会造成污染当前接口。</li>
            </ul>

            <h3 class="font-bold mt-6 mb-2 text-primary-light">14.3 解决方案2：ThreadLocal</h3>
            <ul class="list-disc pl-5">
                <li>可以将整个线程中（单线程）中，存储一个共享值。</li>
                <li>线程拥有一个类似 Map 的属性，键值对结构&lt;ThreadLocal对象，值&gt;。</li>
            </ul>

            <h3 class="font-bold mt-6 mb-2 text-primary-light">14.4 ThreadLocal应用</h3>
            <p>一个线程共享同一个 ThreadLocal，在整个流程中任一环节可以存值或取值。</p>

            <h4 class="font-bold mt-4 mb-2 text-primary-light">14.4.1 参数绑定</h4>
            <p>在 DBUtils中，将当前 Connection对象添加到 ThreadLocal 中。</p>
            <div class="code-block">
                <span class="lang">java</span>
                <pre>ThreadLocal&lt;Connection&gt; tl = new ThreadLocal&lt;Connection&gt;();

//getConnection 方法修改
public static Connection getConnection(){
  Connection conn = tl.get();//获取线程中存储的 Connection 对象
  if(conn ==null){
    conn = ds.getConnection();//从连接池中获取一个连接
    tl.set(conn);//存储到线程对象中。
  }
  return conn;
}
 //关闭所有连接  增加 tl.remove(); 移除
    public static void closeAll(ResultSet rs, Statement st, Connection conn) {
        try {
            if (rs != null) {
                rs.close();
            }
            if (st != null) {
                st.close();
            }
            if (conn != null) {
                conn.close();
                tl.remove();//将 conn 移除。
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }</pre>
            </div>
        </div>
    </section>

    <!-- Transaction Encapsulation Section -->
    <section id="transaction-encapsulation" class="section">
        <h2 class="section-title">十五、事务的封装</h2>
        <div class="card">
            <p>将事务的开启、提交、回滚都封装在工具类中，业务层调用即可。</p>
            
            <h3 class="font-bold mt-6 mb-2 text-primary-light">15.1 完善工具类</h3>
            <div class="code-block">
                <span class="lang">java</span>
                <pre>//开启事务
public static void begin(){
    Connection connection = getConnection();
    try {
        connection.setAutoCommit(false);
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

//提交事务
public static void commit(){
    Connection connection = getConnection();
    try {
        connection.commit();
    } catch (SQLException e) {
        e.printStackTrace();
    }finally {
        DBUtils.closeAll(connection,null,null);
    }
}
//回滚事务
public static void rollback(){
    Connection connection = getConnection();
    try {
        connection.rollback();
    } catch (SQLException e) {
        e.printStackTrace();
    }finally {
        DBUtils.closeAll(connection,null,null);
    }
}</pre>
            </div>
        </div>
    </section>

    <!-- Three-Tier Architecture Section -->
    <section id="three-tier" class="section bg-gray-50">
        <h2 class="section-title">十六、三层架构</h2>
        <div class="card">
            <h3 class="card-title"><i class="fas fa-layer-group"></i> 16.1 什么是三层</h3>
            <ul class="list-disc pl-5">
                <li><span class="font-medium">表示层：</span>
                    <ul class="list-disc pl-5 ml-4">
                        <li>命名：XXXView</li>
                        <li>职责：收集用户的数据和需求、展示数据。</li>
                    </ul>
                </li>
                <li><span class="font-medium">业务逻辑层：</span>
                    <ul class="list-disc pl-5 ml-4">
                        <li>命名：XXXServiceImpl</li>
                        <li>职责：数据加工处理、调用DAO完成业务实现、控制事务。</li>
                    </ul>
                </li>
                <li><span class="font-medium">数据访问层：</span>
                    <ul class="list-disc pl-5 ml-4">
                        <li>命名：XXXDaoImpl</li>
                        <li>职责：向业务层提供数据，将业务层加工后的数据同步到数据库。</li>
                    </ul>
                </li>
            </ul>

            <h4 class="font-bold mt-4 mb-2 text-primary-light">三层架构核心流程</h4>
            <div class="diagram-container">
                <div class="mermaid">
                    graph LR
                    A[表示层 View] -->|请求| B[业务逻辑层 Service]
                    B -->|调用| C[数据访问层 DAO]
                    C -->|数据库操作| D[(数据库)]
                    D -->|返回数据| C
                    C -->|返回数据| B
                    B -->|返回结果| A
                </div>
            </div>

            <h3 class="font-bold mt-6 mb-2 text-primary-light">16.2 三层架构项目搭建（按开发步骤）</h3>
            <ul class="list-disc pl-5">
                <li>utils 存放工具类（DBUtils）</li>
                <li>entity 存放实体类（Person）</li>
                <li>dao 存放 DAO 接口（PersonDao）</li>
                <li>impl 存放 DAO 接口实现类（PersonDaoImpl）</li>
                <li>service 存放 service 接口（PersonService）</li>
                <li>impl 存放 service 接口实现类（PersonServiceImpl）</li>
                <li>view 存放程序启动类（main）</li>
                <li>程序设计时，考虑易修改、易扩展，为Service层和DAO层设计接口，便于未来更换实现类</li>
            </ul>
        </div>
    </section>

    <!-- DaoUtils Section -->
    <section id="dautils" class="section">
        <h2 class="section-title">十七、DaoUtils</h2>
        <div class="card">
            <p>在DAO层中，对数据库表的增、删、改、查操作存在代码冗余，可对其进行抽取封装DaoUtils工具类实现复用。</p>
            
            <h3 class="font-bold mt-6 mb-2 text-primary-light">17.1 commonsUpdate</h3>
            <div class="code-block">
                <span class="lang">java</span>
                <pre>/**
     * 公共处理增、删、改的方法
     * sql语句，参数列表
     *
     * @param sql  执行的sql语句
     * @param args 参数列表。为占位符赋值
     * @return
     */
    public int commonsUpdate(String sql, Object... args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = DBUtils.getConnection();
            preparedStatement = connection.prepareStatement(sql);

            for (int i = 0; i < args.length; i++) {
                preparedStatement.setObject(i + 1, args[i]);
            }

            return preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.closeAll(null, preparedStatement, null);
        }
        return 0;
    }</pre>
            </div>

            <h3 class="font-bold mt-6 mb-2 text-primary-light">17.2 commonsSelect</h3>
            <div class="code-block">
                <span class="lang">java</span>
                <pre>/**
     * 公共查询方法 (可查询单个对象，也可查询多个对象,可以查任何一张表)
     *
     * @param sql
     * @param args
     * @return
     */
    //                              select * from t_account
    //                              select * from t_student
    //工具不知道查的是什么  调用者知道
    //封装对象、对象赋值  调用者清楚
    public List&lt;T&gt; commonsSelect(String sql, RowMapper&lt;T&gt; rowMapper, Object... args) {

        List&lt;T&gt; elements = new ArrayList&lt;T&gt;();

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = DBUtils.getConnection();
            preparedStatement = connection.prepareStatement(sql);

            if(args !=null){
                for (int i = 0; i < args.length; i++) {
                    preparedStatement.setObject(i + 1, args[i]);
                }
            }


            resultSet = preparedStatement.executeQuery();

            while (resultSet.next()) {
                //根据查询到的结果完成ORM，如何进行对象的创建及赋值？
                T t = rowMapper.getRow(resultSet);//回调---->调用者提供的一个封装方法ORM
                elements.add(t);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.closeAll(null, preparedStatement, resultSet);
        }
        return elements;
    }</pre>
            </div>
        </div>
    </section>

    <!-- Druid Section -->
    <section id="druid" class="section bg-gray-50">
        <h2 class="section-title">十八、Druid连接池</h2>
        <div class="card">
            <p>在程序初始化时，预先创建指定数量的数据库连接对象存储在池中。当需要连接数据库时，从连接池中取出现有连接；使用完毕后，也不会进行关闭，而是放回池中，实现复用，节省资源。</p>
            
            <h3 class="font-bold mt-6 mb-2 text-primary-light">18.1 Druid 连接池使用步骤</h3>
            <ul class="list-disc pl-5">
                <li>创建 database.properties 配置文件。</li>
                <li>引入druid-1.1.5.jar 文件。</li>
            </ul>

            <h4 class="font-bold mt-4 mb-2 text-primary-light">18.1.1 database.properties配置文件</h4>
            <div class="code-block">
                <span class="lang">properties</span>
                <pre>#连接设置
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/school
username=root
password=root
#<!-- 初始化连接 -->
initialSize=10
#最大连接数量
maxActive=50
#<!-- 最小空闲连接 -->
minIdle=5
#<!-- 超时等待时间以毫秒为单位 60000毫秒/1000等于60秒 -->
maxWait=5000</pre>
            </div>

            <h4 class="font-bold mt-4 mb-2 text-primary-light">18.1.2 连接池工具类</h4>
            <div class="code-block">
                <span class="lang">java</span>
                <pre>public class DbUtils {
	//声明连接池对象
	private static DruidDataSource ds;
	static{
		//实例化配置对象
		Properties properties=new Properties();
		try {
			        //加载配置文件内容
              properties.load(DbUtils.class.getResourceAsStream("database.properties"));
              ds = (DruidDataSource)DruidDataSourceFactory.createDataSource(properties);	
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	//获取连接对象
	public static Connection getConnection() {
		try {
			return ds.getConnection();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
  //释放资源。。
}</pre>
            </div>
        </div>
    </section>

    <!-- Footer -->
    <footer class="footer">
        <div class="container mx-auto">
            <p class="mb-2">技术小馆</p>
            <p><a href="http://www.yuque.com/jtostring" target="_blank">http://www.yuque.com/jtostring</a></p>
        </div>
    </footer>

    <script>
        mermaid.initialize({
            startOnLoad: true,
            theme: 'default',
            flowchart: {
                useMaxWidth: true,
                htmlLabels: true,
                curve: 'basis'
            }
        });
    </script>
</body>
</html>
```